package net.cyue.homework.student.management.db;

import java.math.BigDecimal;
import java.sql.*;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.function.Consumer;

import com.mysql.cj.jdbc.AbandonedConnectionCleanupThread;


public class DBConnectionPool {

    public static class Parameter {
        public Object value;
        // java.sql.Types
        public Integer type = null;

        public Parameter(Object value) {
            this.value = value;
        }
        public Parameter(Object value, int type) {
            this.value = value;
            this.type = type;
        }
    }

    private static boolean DRIVER_INITIALIZED = false;

    private final DBConnectionPoolConfiguration configuration;
    private ConnectionWrapper[] pool;
    private Thread checkThread;
    private volatile boolean closed = false;

    public DBConnectionPool(DBConnectionPoolConfiguration configuration) {
        this(configuration, 10);
    }
    public DBConnectionPool(
        DBConnectionPoolConfiguration configuration,
        int size
    ) {
        if (!DRIVER_INITIALIZED) {
            try {
                Class.forName(configuration.driver);
                DRIVER_INITIALIZED = true;
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.out.println("数据库驱动加载失败");
                System.exit(0);
            }
        }

        this.configuration = configuration;
        this.setSize(size);

        Runtime
            .getRuntime()
            .addShutdownHook(new Thread(this::close));

        // 定时检查连接池中的连接是否可用
        this.checkThread = new Thread(() -> {
            try {
                while(!this.closed) {
                    Thread.sleep(500);
                    for (int i = 0; i < this.pool.length; i++) {
                        ConnectionWrapper connectionWrapper = this.pool[i];
                        if (!connectionWrapper.isValid(2)) {
                            connectionWrapper.close();
                        }
                        if (connectionWrapper.isClosed()) {
                            this.pool[i] = this.getNewConnectionWrapper();
                        }
                    }
                }
            } catch (InterruptedException e) {
            	e.printStackTrace();
            }
        });
        this.checkThread.start();
    }

    private ConnectionWrapper getNewConnectionWrapper() {
        try {
            Connection connection = DriverManager.getConnection(
                this.configuration.url,
                this.configuration.username,
                this.configuration.password
            );
            return new ConnectionWrapper(connection);
        } catch (SQLException e) {
            System.exit(0);
            throw new RuntimeException(e);
        }
    }

    public void close() {
    	this.closed = true;
    	this.checkThread.interrupt();
        for (ConnectionWrapper connectionWrapper : this.pool) {
            connectionWrapper.close();
        }
        Iterator<Driver> it = DriverManager.drivers().iterator();
        while(it.hasNext()) {
        	try {
				DriverManager.deregisterDriver(it.next());
			} catch (SQLException e) {
				e.printStackTrace();
			}
        }
        AbandonedConnectionCleanupThread.checkedShutdown();
    }
    public void setSize(int size) {
        if (size < 0) {
            throw new IllegalArgumentException("size must be greater than 0");
        }

        int nowSize = this.pool != null ? this.pool.length : 0;
        if (nowSize == size) {
            return;
        }
        if (nowSize > size) {
            this.pool = Arrays.copyOfRange(this.pool, 0, size);
            return;
        }

        // size > nowSize
        ConnectionWrapper[] temp = new ConnectionWrapper[0];
        if (this.pool != null) {
            temp = this.pool.clone();
        }
        this.pool = new ConnectionWrapper[size];
        System.arraycopy(temp, 0, this.pool, 0, temp.length);
        for (int i = temp.length; i < size; i++) {
            this.pool[i] = this.getNewConnectionWrapper();
        }
    }

    private synchronized ConnectionWrapper getAwaitConnectionWrapper() {
        int index = -1;
        while (true) {
            for (int i = 0; i < this.pool.length; i++) {
                // 找到一个 可用 且 free 的 wrapper
                if (
                    !this.pool[i].isClosed() &&
                    !this.pool[i].isUsing()
                ) {
                    index = i;
                }
            }
            if (index != -1) {
                break;
            }
            try {
                this.wait(100);
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }
        }
        this.pool[index].busy();
        return this.pool[index];
    }
    public Connection getAwaitConnection() {
        try {
            return this.getAwaitConnectionWrapper().getConnection();
        } catch (ConnectionBusyException e) { // 一般是无有的
            e.printStackTrace();
        }
        return this.getAwaitConnection();
    }


    private void setPreparedStatementParameters(
            PreparedStatement statement,
            Parameter[] parameters
    ) throws SQLException {
        for (int i = 0; i < parameters.length; i++) {
            Parameter p = parameters[i];
            int parameterIndex = i + 1;
            if (p.type != null) {
                statement.setObject(parameterIndex, p.value, p.type);
                continue;
            }
            if (p.value instanceof Integer) {
                statement.setInt(parameterIndex, (Integer) p.value);
            } else if (p.value instanceof Long) {
                statement.setLong(parameterIndex, (Long) p.value);
            } else if (p.value instanceof String) {
                statement.setString(parameterIndex, (String) p.value);
            } else if (p.value instanceof Date) {
                statement.setDate(parameterIndex, (Date) p.value);
            } else if (p.value instanceof Boolean) {
                statement.setBoolean(parameterIndex, (Boolean) p.value);
            } else if (p.value instanceof Double) {
                statement.setDouble(parameterIndex, (Double) p.value);
            } else if (p.value instanceof Float) {
                statement.setFloat(parameterIndex, (Float) p.value);
            } else if (p.value instanceof BigDecimal) {
                statement.setBigDecimal(parameterIndex, (BigDecimal) p.value);;
            } else if (p.value instanceof byte[]) {
                statement.setBytes(parameterIndex, (byte[]) p.value);
            } else if (p.value instanceof Blob) {
                statement.setBlob(parameterIndex, (Blob) p.value);
            } else if (p.value instanceof Clob) {
                statement.setClob(parameterIndex, (Clob) p.value);
            } else if (p.value instanceof Ref) {
                statement.setRef(parameterIndex, (Ref) p.value);
            } else if (p.value instanceof Array) {
                statement.setArray(parameterIndex, (Array) p.value);
            } else if (p.value instanceof SQLXML) {
                statement.setSQLXML(parameterIndex, (SQLXML) p.value);
            } else {
                statement.setObject(parameterIndex, p.value);
            }
        }
    }
    public void setPreparedStatementParameters(
            PreparedStatement statement,
            List<Parameter> parameterList
    ) throws SQLException {
        Parameter[] parameters = parameterList.toArray(new Parameter[0]);
        this.setPreparedStatementParameters(statement, parameters);
    }
    public void setPreparedStatementParameters(
            PreparedStatement statement,
            Object[] values
    ) throws SQLException {
        Parameter[] parameters = new Parameter[values.length];
        for (int i = 0; i < values.length; i++) {
            parameters[i] = new Parameter(values[i]);
        }
        this.setPreparedStatementParameters(statement, parameters);
    }

    // 普通款
    public ResultSet query(String sql) throws SQLException {
        try {
            ConnectionWrapper wrapper = this.getAwaitConnectionWrapper();
            Connection connection = wrapper.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            wrapper.free();
            return resultSet;
        } catch (ConnectionBusyException e) { // 一般无有，不处理
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @throws SQLException 抛的 rollback 的异常
     * */
    public int transactionUpdate(String sql) throws SQLException {
        ConnectionWrapper wrapper = this.getAwaitConnectionWrapper();
        Connection connection = null;
        try {
            connection = wrapper.getConnection();
        } catch (ConnectionBusyException e) { // 一般无有，不处理
            e.printStackTrace();
            return 0;
        }
        try {
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            int rows = statement.executeUpdate(sql);
            connection.commit();
            statement.close();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            wrapper.free();
        }
        return 0;
    }

    /**
     * @throws SQLException 抛的 rollback 的异常
     * */
    public int transactionPreparedUpdate(
        String sql,
        List<Parameter> parameterList
    ) throws SQLException {
        Connection connection = this.getAwaitConnection();
        try {
            connection.setAutoCommit(false);
            PreparedStatement statement = connection.prepareStatement(sql);
            this.setPreparedStatementParameters(statement, parameterList);
            int rows = statement.executeUpdate();
            connection.commit();
            statement.close();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            connection.close();
        }
        return 0;
    }

    /**
     * @throws SQLException 抛的 rollback 的异常
     * */
    public int transactionPreparedUpdate(
        String sql,
        Parameter[] parameters
    ) throws SQLException {
        Connection connection = this.getAwaitConnection();
        try {
            connection.setAutoCommit(false);
            PreparedStatement statement = connection.prepareStatement(sql);
            this.setPreparedStatementParameters(statement, parameters);
            int rows = statement.executeUpdate();
            connection.commit();
            statement.close();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            connection.close();
        }
        return 0;
    }

    /**
     * @throws SQLException 抛的 rollback 的异常
     * */
    public int transactionPreparedUpdate(
        String sql,
        Object[] values
    ) throws SQLException {
        Connection connection = this.getAwaitConnection();
        try {
            connection.setAutoCommit(false);
            PreparedStatement statement = connection.prepareStatement(sql);
            this.setPreparedStatementParameters(statement, values);
            int rows = statement.executeUpdate();
            connection.commit();
            statement.close();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            connection.close();
        }
        return 0;
    }


    // 回调款
    public void useConnection(Consumer<Connection> work) {
        ConnectionWrapper wrapper = this.getAwaitConnectionWrapper();
        wrapper.useConnection(work);
    }

    public void transaction(SQLConsumer<Connection> work) {
        this.useConnection(connection -> {
            try {
                try {
                    connection.setAutoCommit(false);
                    work.accept(connection);
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                    connection.rollback();
                } finally {
                    connection.setAutoCommit(true);
                }
            } catch (SQLException e){ //rollback 和 setAutoCommit 的异常，不做处理
                e.printStackTrace();
            }
        });
    }

    public void transactionQuery(
        String sql,
        SQLConsumer<ResultSet> finished
    ) {
        this.transaction(
            connection -> {
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
                finished.accept(resultSet);
                if (!resultSet.isClosed()) {
                    resultSet.close();
                }
                if (!statement.isClosed()) {
                    statement.close();
                }
            }
        );
    }

    public void transactionUpdate(
        String sql,
        SQLConsumer<Integer> finished
    ) {
        this.transaction(
            connection -> {
                Statement statement = connection.createStatement();
                int rows = statement.executeUpdate(sql);
                finished.accept(rows);
                if(!statement.isClosed()) {
                    statement.close();
                }
            }
        );
    }


    public void transactionPreparedQuery(
        String sql,
        List<Parameter> parameterList,
        SQLConsumer<ResultSet> finished
    ) {
        this.transaction(
            connection -> {
                PreparedStatement statement = connection.prepareStatement(sql);
                this.setPreparedStatementParameters(statement, parameterList);
                ResultSet resultSet = statement.executeQuery();
                finished.accept(resultSet);
                if (!resultSet.isClosed()) {
                    resultSet.close();
                }
            }
        );
    }

    public void transactionPreparedQuery(
        String sql,
        Parameter[] parameters,
        SQLConsumer<ResultSet> finished
    ) {
        this.transaction(
            connection -> {
                PreparedStatement statement = connection.prepareStatement(sql);
                this.setPreparedStatementParameters(statement, parameters);
                ResultSet resultSet = statement.executeQuery();
                finished.accept(resultSet);
                if (!resultSet.isClosed()) {
                    resultSet.close();
                }
            }
        );
    }


    /**
     * @throws SQLException 抛的 rollback 与 serAutoCommit 的异常
     * */
    public void transactionPreparedQuery(
        String sql,
        Object[] values,
        SQLConsumer<ResultSet> finished
    ) throws SQLException {
        this.transaction(
            connection -> {
                PreparedStatement statement = connection.prepareStatement(sql);
                this.setPreparedStatementParameters(statement, values);
                ResultSet resultSet = statement.executeQuery();
                finished.accept(resultSet);
                if (!resultSet.isClosed()) {
                    resultSet.close();
                }
            }
        );
    }

    public void transactionPreparedUpdate(
        String sql,
        Parameter[] parameters,
        SQLConsumer<Integer> finished
    ) {
        this.transaction(
            connection -> {
                PreparedStatement statement = connection.prepareStatement(sql);
                this.setPreparedStatementParameters(statement, parameters);
                int rows = statement.executeUpdate();
                finished.accept(rows);
                if(!statement.isClosed()) {
                    statement.close();
                }
            }
        );
    }

    /**
     * @throws SQLException 抛的 rollback 与 serAutoCommit 的异常
     * */
    public void transactionPreparedUpdate(
        String sql,
        List<Parameter> parameterList,
        SQLConsumer<Integer> finished
    ) throws SQLException {
        this.transaction(
            connection -> {
                PreparedStatement statement = connection.prepareStatement(sql);
                this.setPreparedStatementParameters(statement, parameterList);
                int rows = statement.executeUpdate();
                finished.accept(rows);
                if(!statement.isClosed()) {
                    statement.close();
                }
            }
        );
    }

    public void transactionPreparedUpdate(
        String sql,
        Object[] values,
        SQLConsumer<Integer> finished
    ) {
        this.transaction(
            connection -> {
                PreparedStatement statement = connection.prepareStatement(sql);
                this.setPreparedStatementParameters(statement, values);
                int rows = statement.executeUpdate();
                finished.accept(rows);
                if(!statement.isClosed()) {
                    statement.close();
                }
            }
        );
    }
}

